package com.ndp.fb.rdb.db.impl;

import com.alibaba.dubbo.config.annotation.Service;
import com.ndp.fb.constant.CommonConstants;
import com.ndp.fb.rdb.api.UserRdbServices;
import com.ndp.fb.rdb.db.DaoTemplate;
import com.ndp.fb.rdb.db.PstAssign;
import com.ndp.fb.rdb.db.impl.mapper.DefaultRowMapper;
import com.ndp.fb.rdb.model.User;
import com.ndp.fb.rdb.model.UserExt;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.PreparedStatement;
import java.util.List;

/**
 * Created by Jacky on 2015/1/22.
 */
@Service
public class UserRdbServiceImpl extends DaoTemplate implements UserRdbServices {
    private final static String ALL_FIELD = " id,country,email,locked,nickname, password,salt, status,ticket,timezone_id," +
            "timezone_name,username,update_time ,org_id,invitation_code,badgeNum,language";
    private final static String TABLE = " user ";
    private Logger logger = LoggerFactory.getLogger(getClass());

    @Override
    public User findByEmail(String email) {
        StringBuilder sql = new StringBuilder();
        sql.append(CommonConstants.SELECT + ALL_FIELD + CommonConstants.FROM + TABLE);
        sql.append(CommonConstants.WHERE + "email='" + email + "'");
        sql.append(" and status = 0");
        List<User> userlist = getJdbcTemplate().query(sql.toString(), DefaultRowMapper.autoRowMapper(User.class));
        return userlist.size() > 0 ? userlist.get(0) : null;
    }

    @Override
    public User findByUsername(String name) {
        logger.debug("find user name=" + name);
        StringBuilder sql = new StringBuilder();
        sql.append(CommonConstants.SELECT + ALL_FIELD + CommonConstants.FROM + TABLE);
        sql.append(CommonConstants.WHERE + "username='" + name + "'");
        sql.append(" and status = 0");
        List<User> userlist = getJdbcTemplate().query(sql.toString(), DefaultRowMapper.autoRowMapper(User.class));
        return userlist.size() > 0 ? userlist.get(0) : null;
    }

    @Override
    public Long save(final User user) {
        return saveResultId(user);
    }

    @Override
    public void update(final User user) {
        final String sql = "UPDATE user SET " +
//                "company = ?, " +
                "country = ?, " +
                "email = ?, " +
                "locked = ?, " +
                "nickname = ?, " +
                "password = ?, " +
                "salt = ?, " +
                "status = ?, " +
                "ticket = ?, " +
                "timezone_id = ?, " +
                "timezone_name = ?, " +
                "username = ?, " +
//                "website = ?, " +
                "update_time = ?, " +
                "org_id = ?," +
                "invitation_code = ?, " +
                "badgeNum = ? ,"+
                "language = ? "+
                "WHERE id =?";
        getJdbcTemplate().update(connection -> {
            PreparedStatement ps = connection.prepareStatement(sql);
//                ps.setObject(1, user.getCompany());
            ps.setObject(1, user.getCountry());
            ps.setObject(2, user.getEmail());
            ps.setObject(3, user.getLocked());
            ps.setObject(4, user.getNickname());
            ps.setObject(5, user.getPassword());
            ps.setObject(6, user.getSalt());
            ps.setObject(7, user.getStatus());
            ps.setObject(8, user.getTicket());
            ps.setObject(9, user.getTimezoneId());
            ps.setObject(10, user.getTimezoneName());
            ps.setObject(11, user.getUsername());
//                ps.setObject(13, user.getWebsite());
            ps.setObject(12, user.getUpdateTime());
            ps.setObject(13,user.getOrgId());
            ps.setObject(14,user.getInvitationCode());
            ps.setObject(15,user.getBadgeNum());
            ps.setObject(16,user.getLanguage());
            ps.setObject(17,user.getId());
            return ps;
        });
    }

    @Override
    public List<User> getAllUsers() {
        StringBuilder sql = new StringBuilder();
        sql.append(CommonConstants.SELECT + ALL_FIELD + CommonConstants.FROM + TABLE);
        return getJdbcTemplate().query(sql.toString(), DefaultRowMapper.autoRowMapper(User.class));
    }

    @Override
    public List<UserExt> getAllUserExts() {
        String sql = "SELECT u.ticket, u.username,u.nickname,u.update_time,u.org_id, org.`name`,u.timezone_id,u.timezone_name,\n" +
                " GROUP_CONCAT(ur.role_id SEPARATOR '|') role_id\n" +
                " from `user` u  , user_role ur ,organization org\n" +
                "where ur.user_id = u.id and u.org_id = org.id\n" +
                "GROUP BY u.ticket, u.username,u.nickname,u.update_time, org.`name` ";
        return getJdbcTemplate().query(sql,DefaultRowMapper.autoRowMapper(UserExt.class));
    }

    @Override
    public User getAdminUserByOrgId(Long orgId) {
        String sql = "select * from user u join user_role r on u.id= r.user_id where r.role_id =1 and u.org_id = ?";
        List<User> userList =  getJdbcTemplate().query(sql,new Object[]{orgId},DefaultRowMapper.autoRowMapper(User.class));
        if(userList !=null && userList.size()>0 ){
            return userList.get(0);
        }else{
            return null;
        }
    }

    @Override
    public User getUserByTicket(String ticket) {
        String sql = "select * from user where ticket = ?";
        return findOne(sql, new Object[]{ticket}, DefaultRowMapper.autoRowMapper(User.class));
    }

    @Override
    public List<User> getAllUsersByOrgId(Long orgId) {
        String sql = "select * from user u where u.org_id = ?";
        return getJdbcTemplate().query(sql,new Object[]{orgId},DefaultRowMapper.autoRowMapper(User.class));
    }

    @Override
    public List<Long> getRoleByUser(Long userId)
    {
        String sql = "select u.role_id from user_role u where u.user_id = ?";
        return getJdbcTemplate().queryForList(sql, new Object[]{userId}, Long.class);
    }

    @Override
    public void saveUserRole(final Long userId, final Long roleId)
    {
        String sql = "INSERT INTO user_role " +
                "(user_id, role_id) " +
                "VALUES (?,?)";
        PstAssign pst = ps -> {
            ps.setObject(1, userId);
            ps.setObject(2, roleId);
        };
        save(sql, pst);
    }

	@Override
	public void update(String userId, Long spent, int effective_img_num, int hot_img_num, Long effective_img_spent) {
		 final String sql = "UPDATE user SET " +
               "spent = ?, " +
               "effective_img_num = ?, " +
               "hot_img_num = ?, " +
               "effective_img_spent = ? " +
               "WHERE ticket =?";
       getJdbcTemplate().update(connection -> {
           PreparedStatement ps = connection.prepareStatement(sql);
           ps.setObject(1, spent);
           ps.setObject(2, effective_img_num);
           ps.setObject(3, hot_img_num);
           ps.setObject(4, effective_img_spent);
           ps.setObject(5, userId);
           return ps;
       });
	}

    @Override
    public List<User> findByOrgIdAndConnectionOnjectId(Long orgId, Long fbConnectionObjectId) {
        String sql = "select distinct user.* from user,fb_user,fb_userpermissions_adaccounts fba,entrust_account_connection_objects " +
                "where org_id = ? and `user`.ticket = fb_user.user_id and fba.fb_user_id = fb_user.fb_user_id " +
                "and entrust_account_connection_objects.fb_account_id = fba.fb_account_id " +
                "and entrust_account_connection_objects.fb_connection_object_id = ?";
        return getJdbcTemplate().query(sql,new Object[]{orgId, fbConnectionObjectId},DefaultRowMapper.autoRowMapper(User.class));
    }

    @Override
    public List<UserExt> getAllUserExtsByOrgId(Long orgId) {
        String sql = "SELECT u.ticket, u.username,u.nickname,u.update_time,u.org_id, org.`name`,u.timezone_id,u.timezone_name,\n" +
                " GROUP_CONCAT(ur.role_id SEPARATOR '|') role_id\n" +
                " from `user` u  , user_role ur ,organization org\n" +
                "where ur.user_id = u.id and u.org_id = org.id and u.org_id =? \n" +
                "GROUP BY u.ticket, u.username,u.nickname,u.update_time, org.`name` ";
        return getJdbcTemplate().query(sql,new Object[]{orgId},DefaultRowMapper.autoRowMapper(UserExt.class));
    }

    @Override
    public List<User> findByUsernameOrEmail(String usernameOrEmailStr,String userid,Long orgId) {
        String sql = "SELECT * FROM `user` t WHERE (t.username LIKE '"+ usernameOrEmailStr
                +"%' || t.email LIKE '"+ usernameOrEmailStr +"%') AND t.org_id="+ orgId +" AND t.status =0 AND t.ticket!='"+userid+"'";
        return getJdbcTemplate().query(sql,DefaultRowMapper.autoRowMapper(User.class));
    }

    @Override
    public void deleteUser(Long userId) {
        final String sql = "UPDATE user SET " +
                "status = 1 " +
                "WHERE id =?";
        getJdbcTemplate().update(connection -> {
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setObject(1, userId);
            return ps;
        });
    }
}
